#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
生成CPS基础数据同步专用报表模板的SQL插入语句
基于需求文档中的SQL，创建支持动态平台选择和时间筛选的模板
"""
import json

# 平台配置
PLATFORM_CONFIGS = {
    'tb': {
        'name': '淘宝',
        'tables': [
            ('tds.tds_tb_cps_product_detail', True),
            ('ods.ods_tb_cps_product_detail', False),
            ('tds.tds_tb_cps_product_detail_selection', True),
            ('ods.ods_tb_cps_product_detail_selection', False)
        ],
        'note': 'tds_tb_cps_product_campaign_resource表主要用于tbc平台（通过JOIN），为避免重复统计，只在tbc平台统计'
    },
    'tbc': {
        'name': '淘宝团长',
        'tables': [
            ('tds.tds_tb_cps_product_campaign_resource', True),
            ('ods.ods_tb_cps_product_campaign_resource', False),
            ('tds.tds_tb_cps_product_list_activity_captain_activity', True),
            ('ods.ods_tb_cps_product_list_activity_captain_activity', False)
        ],
        'note': 'tbc平台有两个DWS SQL文件，统计所有相关表的product_id'
    },
    'tbb': {
        'name': '淘宝商单广场',
        'tables': [
            ('tds.tds_tb_cps_product_list_activity_business_order', True),
            ('ods.ods_tb_cps_product_list_activity_business_order', False)
        ]
    },
    'dy': {
        'name': '抖音',
        'tables': [
            ('tds.tds_dy_cps_product_detail', True),
            ('ods.ods_dy_cps_product_detail', False),
            ('tds.tds_dy_cps_product_list', True),
            ('ods.ods_dy_cps_product_list', False),
            ('tds.tds_hdk_cps_dy_product_list', True),
            ('ods.ods_hdk_cps_dy_product_list', False),
            ('tds.tds_hdk_cps_dy_product_detail', True),
            ('ods.ods_hdk_cps_dy_product_detail', False)
        ]
    },
    'dyc': {
        'name': '抖音招商团长',
        'tables': [
            ('ods.ods_dy_product_list', False, 'CAST(product_id AS STRING)'),
            ('ods.ods_dy_product_detail', False, 'CAST(product_id AS STRING)'),
            ('tds.tds_dy_product_detail', True, 'CAST(product_id AS STRING)'),
            ('ods.ods_dyc_cps_product_list_captain', False),
            ('tds.tds_dyc_cps_product_list_captain', True),
            ('ods.ods_dyc_cps_product_list_captain_category', False),
            ('tds.tds_dyc_cps_product_list_captain_category', True)
        ]
    },
    'jd': {
        'name': '京东',
        'tables': [
            ('tds.tds_jd_cps_product_list', True),
            ('ods.ods_jd_cps_product_list', False),
            ('tds.tds_jd_cps_product_selected', True),
            ('ods.ods_jd_cps_product_selected', False),
            ('tds.tds_jd_cps_product_guess_you_like', True),
            ('ods.ods_jd_cps_product_guess_you_like', False)
        ]
    },
    'pdd': {
        'name': '拼多多',
        'tables': [
            ('tds.tds_pdd_cps_product_list', True),
            ('ods.ods_pdd_cps_product_list', False),
            ('tds.tds_pdd_cps_product_detail', True),
            ('ods.ods_pdd_cps_product_detail', False),
            ('tds.tds_pdd_cps_product_recommend', True),
            ('ods.ods_pdd_cps_product_recommend', False)
        ]
    },
    'vph': {
        'name': '唯品会',
        'tables': [
            ('tds.tds_vph_cps_product_keyword_query_list', True),
            ('ods.ods_vph_cps_product_keyword_query_list', False),
            ('tds.tds_vph_cps_product_detail', True),
            ('ods.ods_vph_cps_product_detail', False)
        ]
    }
}

def generate_platform_source_sql(platforms):
    """生成平台源表统计SQL"""
    sql_parts = []
    
    for platform in platforms:
        if platform not in PLATFORM_CONFIGS:
            continue
            
        config = PLATFORM_CONFIGS[platform]
        table_parts = []
        
        for table_info in config['tables']:
            table_name = table_info[0]
            need_distinct = table_info[1]
            cast_expr = table_info[2] if len(table_info) > 2 else 'product_id'
            
            if need_distinct:
                table_parts.append(f"        SELECT DISTINCT {cast_expr} AS product_id FROM {table_name}")
            else:
                table_parts.append(f"        SELECT {cast_expr} AS product_id FROM {table_name}")
        
        tables_sql = "\n        UNION ALL\n".join(table_parts)
        
        # 添加平台注释
        note_comment = f"    -- 说明：{config['note']}" if 'note' in config else ""
        
        sql_part = f"""    -- ========== {config['name']}({platform}) ==========
{note_comment}
    SELECT '{platform}' AS platform,
           COUNT(DISTINCT product_id) AS source_total_count
    FROM (
{tables_sql}
    ) t"""
        
        sql_parts.append(sql_part)
    
    return "\n\n    UNION ALL\n\n".join(sql_parts)

def generate_sql_template():
    """生成SQL模板"""
    # 生成平台源表SQL占位符
    platform_source_sql = "{platform_source_sql}"
    selected_platforms = "{selected_platforms}"
    time_filter = "{time_filter}"
    sync_rate_threshold = "{sync_rate_threshold}"
    output_sql = "{output_sql}"
    
    sql_template = f"""/*
 * CPS基础数据同步专用报表模板
 * 描述: 统计各平台从原始层到DWS的数据同步情况
 * 功能:
 *   1. 合并每个平台所有TDS和ODS表的product_id,统一去重统计作为原始层总数
 *   2. 统计DWS层各平台的总商品数
 *   3. 统计DWS层各平台上架商品数(is_on_sales=0)
 *   4. 统计DWS层各平台下架商品数(is_on_sales=1)
 *   5. 计算同步率(DWS总数/原始层总数)
 * 数据说明:
 *   - TDS层: 实时临时数据,保留7天,可能有重复(需去重)
 *   - ODS层: T+1全量数据,长期保存,主键唯一(无需去重)
 *   - DWS层: 从TDS加工而来的宽表数据
 */

-- =============================================
-- 第一部分: 合并每个平台所有TDS和ODS表的product_id,统一去重统计
-- =============================================
WITH platform_source_union AS (
{platform_source_sql}
),

-- =============================================
-- 第二部分: 统计DWS层各平台的数据情况
-- =============================================
dws_stats AS (
    SELECT
        platform,
        MAX(platform_all) AS platform_all,
        COUNT(*) AS dws_total_count,
        SUM(CASE WHEN is_on_sales = 0 THEN 1 ELSE 0 END) AS dws_on_sale_count,
        SUM(CASE WHEN is_on_sales = 1 THEN 1 ELSE 0 END) AS dws_off_sale_count
    FROM dws.dws_cps_product_base_info
    WHERE platform IN ({selected_platforms}){time_filter}
    GROUP BY platform
),

-- =============================================
-- 第三部分: 合并结果并计算同步率
-- =============================================
platform_detail AS (
    SELECT
        src.platform AS '平台',
        COALESCE(dws.platform_all, src.platform) AS '平台汇总',
        'TDS+ODS合并去重' AS '原始层(源表)',
        src.source_total_count AS '原始层商品总数',
        COALESCE(dws.dws_total_count, 0) AS '目标表商品总数',
        COALESCE(dws.dws_on_sale_count, 0) AS '目标表上架商品数',
        COALESCE(dws.dws_off_sale_count, 0) AS '目标表下架商品数',
        CONCAT(
            ROUND(
                CASE
                    WHEN src.source_total_count > 0
                    THEN (COALESCE(dws.dws_total_count, 0) * 100.0 / src.source_total_count)
                    ELSE 0
                END,
                2
            ),
            '%'
        ) AS '同步率',
        CASE
            WHEN src.source_total_count = 0 THEN '无原始数据'
            WHEN COALESCE(dws.dws_total_count, 0) = 0 THEN '未同步'
            WHEN (COALESCE(dws.dws_total_count, 0) * 100.0 / src.source_total_count) < {sync_rate_threshold} THEN '同步率过低'
            ELSE '正常'
        END AS '数据状态',
        1 AS sort_order
    FROM platform_source_union src
    LEFT JOIN dws_stats dws ON src.platform = dws.platform
)

{output_sql}

ORDER BY sort_order, `平台`;"""
    
    return sql_template

# 定义param_schema
param_schema = {
    "type": "object",
    "properties": {
        "data_source_config": {
            "type": "object",
            "title": "数据源配置",
            "description": "选择StarRocks数据源，库和表名使用默认配置",
            "properties": {
                "data_source_id": {
                    "type": "integer",
                    "title": "数据源ID",
                    "description": "StarRocks数据源配置ID"
                }
            },
            "required": ["data_source_id"]
        },
        "platform_selection": {
            "type": "object",
            "title": "平台选择",
            "description": "选择要监控的平台和汇总选项",
            "properties": {
                "selected_platforms": {
                    "type": "array",
                    "title": "选择平台",
                    "description": "选择要监控的平台，支持多选",
                    "items": {
                        "type": "string",
                        "enum": list(PLATFORM_CONFIGS.keys())
                    },
                    "default": ["dy"]
                },
                "include_summary": {
                    "type": "boolean",
                    "title": "包含平台汇总",
                    "description": "是否在结果中包含平台汇总信息（全平台汇总行）",
                    "default": True
                }
            },
            "required": ["selected_platforms"]
        },
        "time_filter": {
            "type": "object",
            "title": "时间筛选",
            "description": "基于etl_date字段的时间筛选",
            "properties": {
                "time_range_type": {
                    "type": "string",
                    "title": "时间范围类型",
                    "description": "选择时间范围类型",
                    "enum": ["all", "recent", "custom"],
                    "default": "all"
                },
                "time_start": {
                    "type": "string",
                    "title": "开始时间",
                    "description": "开始时间，格式：YYYY-MM-DD HH:mm:ss",
                    "format": "datetime"
                },
                "time_end": {
                    "type": "string",
                    "title": "结束时间",
                    "description": "结束时间，格式：YYYY-MM-DD HH:mm:ss",
                    "format": "datetime"
                }
            },
            "required": ["time_range_type"]
        },
        "threshold_config": {
            "type": "object",
            "title": "阈值配置",
            "description": "配置同步率阈值",
            "properties": {
                "sync_rate_threshold": {
                    "type": "integer",
                    "title": "同步率阈值",
                    "description": "同步率低于此阈值时，数据状态显示为'同步率过低'",
                    "default": 80,
                    "minimum": 0,
                    "maximum": 100
                }
            },
            "required": ["sync_rate_threshold"]
        },
        "display_fields": {
            "type": "display_fields",
            "format": "display_fields",
            "title": "显示字段",
            "description": "选择要在报表中显示的字段",
            "options": [
                {"value": "platform", "label": "平台"},
                {"value": "platform_summary", "label": "平台汇总"},
                {"value": "source_layer", "label": "原始层(源表)"},
                {"value": "source_total_count", "label": "原始层商品总数"},
                {"value": "dws_total_count", "label": "目标表商品总数"},
                {"value": "dws_on_sale_count", "label": "目标表上架商品数"},
                {"value": "dws_off_sale_count", "label": "目标表下架商品数"},
                {"value": "sync_rate", "label": "同步率"},
                {"value": "data_status", "label": "数据状态"}
            ],
            "default": [
                "platform",
                "platform_summary", 
                "source_layer",
                "source_total_count",
                "dws_total_count",
                "dws_on_sale_count",
                "dws_off_sale_count",
                "sync_rate",
                "data_status"
            ]
        }
    },
    "required": ["data_source_config", "platform_selection", "time_filter", "threshold_config"]
}

# 定义default_params
default_params = {
    "data_source_config": {
        "data_source_id": None
    },
    "platform_selection": {
        "selected_platforms": ["dy"],
        "include_summary": True
    },
    "time_filter": {
        "time_range_type": "all",
        "time_start": None,
        "time_end": None
    },
    "threshold_config": {
        "sync_rate_threshold": 80
    },
    "display_fields": [
        "platform",
        "platform_summary",
        "source_layer", 
        "source_total_count",
        "dws_total_count",
        "dws_on_sale_count",
        "dws_off_sale_count",
        "sync_rate",
        "data_status"
    ],
    "sql_template": generate_sql_template()
}

def escape_for_mysql(json_str):
    """转义JSON字符串用于MySQL"""
    result = json_str.replace('\\', '\\\\')
    result = result.replace("'", "''")
    return result

def generate_insert_sql():
    """生成插入SQL"""
    # 生成动态SQL模板
    platform_source_sql = generate_platform_source_sql(list(PLATFORM_CONFIGS.keys()))
    
    # 生成输出SQL部分
    output_sql = """-- 汇总所有平台的数据（放在第一行）
SELECT
    'ALL' AS '平台',
    '全平台汇总' AS '平台汇总',
    '所有平台汇总' AS '原始层(源表)',
    SUM(`原始层商品总数`) AS '原始层商品总数',
    SUM(`目标表商品总数`) AS '目标表商品总数',
    SUM(`目标表上架商品数`) AS '目标表上架商品数',
    SUM(`目标表下架商品数`) AS '目标表下架商品数',
    CONCAT(
        ROUND(
            CASE
                WHEN SUM(`原始层商品总数`) > 0
                THEN (SUM(`目标表商品总数`) * 100.0 / SUM(`原始层商品总数`))
                ELSE 0
            END,
            2
        ),
        '%'
    ) AS '同步率',
    CASE
        WHEN SUM(`原始层商品总数`) = 0 THEN '无原始数据'
        WHEN SUM(`目标表商品总数`) = 0 THEN '未同步'
        WHEN (SUM(`目标表商品总数`) * 100.0 / SUM(`原始层商品总数`)) < {sync_rate_threshold} THEN '同步率过低'
        ELSE '正常'
    END AS '数据状态',
    0 AS sort_order
FROM platform_detail

UNION ALL

-- 各平台明细数据
SELECT
    `平台`,
    `平台汇总`,
    `原始层(源表)`,
    `原始层商品总数`,
    `目标表商品总数`,
    `目标表上架商品数`,
    `目标表下架商品数`,
    `同步率`,
    `数据状态`,
    `sort_order`
FROM platform_detail

ORDER BY sort_order, `平台`"""
    
    # 更新SQL模板
    default_params["sql_template"] = generate_sql_template().format(
        platform_source_sql=platform_source_sql,
        selected_platforms="{selected_platforms}",
        time_filter="{time_filter}",
        sync_rate_threshold="{sync_rate_threshold}",
        output_sql=output_sql
    )
    
    # 转换为JSON字符串
    param_schema_json = json.dumps(param_schema, ensure_ascii=False, indent=2)
    default_params_json = json.dumps(default_params, ensure_ascii=False, indent=2)
    
    # 转义用于MySQL
    param_schema_escaped = escape_for_mysql(param_schema_json)
    default_params_escaped = escape_for_mysql(default_params_json)
    
    # 生成最终SQL
    sql = f"""-- ============================================
-- 插入CPS基础数据同步专用报表模板
-- ============================================
-- 说明：CPS业务数据质量监控报表模板，支持多平台选择、时间筛选和动态汇总功能

INSERT INTO report_templates 
(template_code, template_name, template_group, description, param_schema, default_params, output_formats, is_active, version)
VALUES 
(
    'cps_data_quality_monitor', 
    'CPS基础数据同步专用报表', 
    'mixed', 
    '统计CPS各平台数据从TDS/ODS到DWS的同步情况，支持多平台选择、时间筛选和动态汇总。计算同步率、上架/下架商品数等关键指标，帮助监控数据同步质量和业务数据完整性。支持淘宝、抖音、京东、拼多多、唯品会等8个平台的数据质量监控。',
    '{param_schema_escaped}',
    '{default_params_escaped}',
    '["html", "excel"]',
    1,
    1
);

-- 验证插入结果
SELECT 
    template_code,
    template_name,
    template_group,
    description,
    is_active,
    version,
    created_at
FROM report_templates
WHERE template_code = 'cps_data_quality_monitor';"""
    
    return sql

if __name__ == "__main__":
    print("正在生成CPS基础数据同步专用报表模板SQL...")
    sql = generate_insert_sql()
    print(sql)
    print("\nSQL生成完成！")
